package com.xm.util;


import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author : xie'lin'feng
 * @version : 1.0
 * @project : e-commerce
 * @description : mysql操作工具类
 * @date : 2020-08-06 19:32
 */
public class DButil {

    /**
     * mysql连接url
     */
    public static final String URL = "jdbc:mysql:///commodity?characterEncoding=utf-8";
    /**
     * 登录账号
     */
    public static final String USERNAME = "root";
    /**
     * 登录密码
     */
    public static final String PWD = "123123";
    /**
     * 获取mysql数据库的连接
     *
     * @return java.sql.Connection
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USERNAME, PWD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 关闭相关资源对象
     * @param rs
     * @param ps
     * @param conn
     */
    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 解析rs数据
     * @param rs
     * @return
     */
    public static List<?> getRsData(Class cls,ResultSet rs){
        List<Object> lst = new ArrayList<Object>();
        try {
            //获取结果集的结构信息:比如字段名，字段个数
            ResultSetMetaData rsMetaData = rs.getMetaData();
            while (rs.next()) {
                //不管查的是哪个类,都可以class.newInstance得到实例化对象
                Object bean = cls.newInstance();
                Field[] fields = cls.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    field.set(bean,rs.getObject(field.getName()));
                }
                lst.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }catch (InstantiationException e) {
            e.printStackTrace();
        }
        return lst;
    }
    /**
     * 统计单表记录数
     * @param cls
     * @return
     */
    public static Integer count(Class<?> cls){
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        //获取实体类名
        String tableName = cls.getSimpleName();
        String commSql = "select count(1) from `" + tableName+"`";
        conn = getConnection();

        System.out.println("执行单表查询语句："+commSql);
        System.out.println("-----------------------------------------------------------------");
        try {
            ps = conn.prepareStatement(commSql);
            rs = ps.executeQuery();
            if(rs.next()){
               count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(rs,ps,conn);
        }
        return count;
    }
    /**
     * 公共查询方法
     * @param cls
     * @return java.util.List
     */
    public static List<?> queryAll(Class<?> cls) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Object> lst = new ArrayList<Object>();

        try {
            //获取实体类名
            String tableName = cls.getSimpleName();
            String commSql = "select * from `" + tableName+"`";

            conn = getConnection();

            System.out.println("执行单表查询语句："+commSql);
            System.out.println("-----------------------------------------------------------------");
            ps = conn.prepareStatement(commSql);

            rs = ps.executeQuery();

            //获取结果集的结构信息:比如字段名，字段个数
            ResultSetMetaData rsMetaData = rs.getMetaData();
            while (rs.next()) {
                //不管查的是哪个类,都可以class.newInstance得到实例化对象
                Object bean = cls.newInstance();
                Field[] fields = cls.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    field.set(bean,rs.getObject(field.getName()));
                }
                lst.add(bean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(rs, ps, conn);
        }
        return lst;
    }
    /**
     * 根据id查询一条数据的方法
     *
     * @param cls
     * @param id
     * @return java.lang.Object
     */
    public static Object queryOne(Class<?> cls, Integer id) {
        Object obj = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String tableName = cls.getSimpleName();
            conn = getConnection(); // 取连接
            String sql = "select * from `" + tableName + "` where ";
            Field[] df = cls.getDeclaredFields();
            //取出你要查询的类的第一个属性，作为查询条件拼接成SQL
            sql += df[0].getName() + "=?";
            System.out.println("执行id查询语句："+sql);
            System.out.println("-----------------------------------------------------------------");
            ps = conn.prepareStatement(sql); // 执行对象
            ps.setObject(1, id);
            rs = ps.executeQuery();
            ResultSetMetaData rsMetaData = rs.getMetaData();
            if (rs.next()) {
                obj = cls.newInstance();
                Field[] fields = cls.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    field.set(obj,rs.getObject(field.getName()));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(rs, ps, conn);
        }
        return obj;
    }
    /**
     * 按条件查询
     * @param object
     * @return java.util.List
     */
    public static List<?> queryByCondition(Object object){
        Connection connection = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Object> lst = new ArrayList<Object>();

        Class cls = object.getClass();
        //获取实体类名
        String tableName = cls.getSimpleName();
        String sql = "select * from `" + tableName + "` where 1=1 ";

        //拼接条件
        try {
            Field[] declaredFields = cls.getDeclaredFields();
            for (int i = 0; i < declaredFields.length; i++) {
                //取得该属性操作权限
                declaredFields[i].setAccessible(true);
                //取得该属性的值
                Object val = declaredFields[i].get(object);
                if(null != val){
                    sql += " and " + declaredFields[i].getName() + "= ?";
                }
            }

            System.out.println("执行条件查询语句："+sql);
            System.out.println("-----------------------------------------------------------------");
            ps = connection.prepareStatement(sql);
            //j是为了保证占位符参数不乱
            int j = 1;
            for (int i = 0; i < declaredFields.length; i++) {
                declaredFields[i].setAccessible(true);
                Object val = declaredFields[i].get(object);
                if(null != val){
                    ps.setObject(j, declaredFields[i].get(object));
                    j++;
                }
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsMetaData = rs.getMetaData();
            while (rs.next()) {
                object = cls.newInstance();
               /* for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
                    String columnName = rsMetaData.getColumnName(i + 1);
                    Object columnValue = rs.getObject(columnName);
                    BeanUtils.setProperty(object, columnName, columnValue);
                }*/
                Field[] fields = cls.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    field.set(object,rs.getObject(field.getName()));
                }
                lst.add(object);
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }finally {
            close(rs,ps,connection);
        }

        return lst;
    }
    /**
     *  公共修改方法
     *
     * @param obj
     * @return boolean
     */
    public static boolean update(Object obj) {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class cls = obj.getClass();
            String tableName = cls.getSimpleName();
            conn = getConnection();
            String sql = "update   `" + tableName + "` set ";
            Field[] declaredFields = cls.getDeclaredFields();
            for (int i = 1; i < declaredFields.length; i++) {
                //取得该属性操作权限
                declaredFields[i].setAccessible(true);
                //取得该属性的值
                Object val = declaredFields[i].get(obj);
                //注意：实体类中的属性如果是数值类型，必须使用对应的封装类
                //加个判断,不为空的属性才进行修改
                if(null != val){
                    sql += declaredFields[i].getName() + "=?,";
                }
            }
            //取出SQL语句中要修改的最后一个属性后面的,号
            sql = sql.substring(0,sql.length()-1);
            //固定使用id作为修改条件(id需要放在类第一个属性)
            sql += " where " + declaredFields[0].getName() + "=?";

            System.out.println("执行修改语句："+sql);
            System.out.println("-----------------------------------------------------------------");
            ps = conn.prepareStatement(sql);

            //j是为了保证占位符参数不乱
            int j = 1;
            for (int i = 1; i < declaredFields.length; i++) {
                declaredFields[i].setAccessible(true);
                Object val = declaredFields[i].get(obj);
                if(null != val){
                    ps.setObject(j, declaredFields[i].get(obj));
                    j++;
                }
            }
            declaredFields[0].setAccessible(true);

            ps.setObject(j, declaredFields[0].get(obj));
            count = ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        return count > 0;
    }
    /**
     * 公共根据id刪除方法
     * @param cls
     * @param id
     * @return boolean
     */
    public static boolean delete(Class<?>cls, String id){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            String tableName = cls.getSimpleName();
            conn = getConnection(); // 取连接
            String sql = "delete from `"+tableName+"` where ";
            Field[] fields = cls.getDeclaredFields();
            Field field2 = fields[0];
            field2.setAccessible(true);
            sql+= field2.getName();
            sql+="=?";
            System.out.println("执行id刪除语句："+sql);
            System.out.println("-----------------------------------------------------------------");
            ps = conn.prepareStatement(sql); // 执行对象
            ps.setObject(1, id);
            int count = ps.executeUpdate();
            return count > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            close(null, ps, conn);
        }

    }
    /**
     *  公共添加方法
     * @param obj
     * @return 添加是否成功
     */
    public static boolean insert(Object obj){//根據序列添加
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class cls= obj.getClass();
            String tableName = cls.getSimpleName();
            conn = getConnection(); // 取连接
            Field[] fields = cls.getDeclaredFields();
            String sql = "insert into `"+tableName+"`("+fields[0].getName()+",";
            for (int i = 1; i < fields.length; i++) {
                //判断不为空的属性,就是要新增的字段数据
                Field field = fields[i];
                field.setAccessible(true);
                if(field.get(obj)!=null){
                    sql += field.getName()+",";
                }
            }
            //取出SQL语句中要修改的最后一个属性后面的,号
            sql = sql.substring(0,sql.length()-1)+")";
            sql += " values(null,";
            for (int i = 1; i < fields.length; i++) {
                Field field = fields[i];
                field.setAccessible(true);
                if(field.get(obj)!=null){
                    if(i!=fields.length-1){
                        sql+="?,";
                    }else{
                        sql+="?)";
                    }
                }
            }
            sql = sql.substring(0,sql.length()-1)+")";
            System.out.println("执行新增语句："+sql);
            System.out.println("-----------------------------------------------------------------");
            ps = conn.prepareStatement(sql); // 执行对象
            //j是为了保证占位符参数不乱
            int j = 1;
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                Object val = fields[i].get(obj);
                if(null != val){
                    ps.setObject(j, fields[i].get(obj));
                    j++;
                }
            }
            int count = ps.executeUpdate();
            return count > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            close(null, ps, conn);
        }
    }
    //解析单个rs
    public static Object getRsOne(Class cls,ResultSet rs){
        Object obj = null;
        try {
            if (rs.next()){
                obj = cls.newInstance();
                Field[] fields = cls.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    field.set(obj,rs.getObject(field.getName()));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return obj;
    }
}
